package com.habdqn.attendance.dao.impl;

import com.habdqn.attendance.dao.BaseDao;
import com.habdqn.attendance.dao.UserDao;
import com.habdqn.attendance.entity.Department;
import com.habdqn.attendance.entity.User;
import com.habdqn.attendance.entity.example.UserExample;
import lombok.extern.log4j.Log4j;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @author XingJiejian
 * @date 2019/10/24 0024
 */
@Log4j
public class UserDaoImpl extends BaseDao implements UserDao {
    private String selectSql = "SELECT u.id,u.username,u.password,u.name,u.gender,u.birthday,u.is_admin,u.mobile,u" +
            ".department_id FROM user u ";

    @Override
    public User queryById(Integer id) {
        String sql = selectSql + " WHERE id=?";
        List<User> users = this.getListByResultSet(this.executeQuery(sql, id));
        if (users != null && users.size() > 0) {
            return users.get(0);
        }
        return null;
    }

    @Override
    public List<User> queryAllByLimit(int offset, int limit) {
        String sql = selectSql + " LIMIT ?,?";
        return this.getListByResultSet(this.executeQuery(sql, offset, limit));
    }


    @Override
    public List<User> queryAll(UserExample user) {
        List<Object> paramList = new ArrayList<>();
        // 组装SQL条件和参数
        String sql = this.exampleWhereClause(user, paramList, true, false);
        log.info("sql:[" + sql + "]");
        log.info("params:" + Arrays.toString(paramList.toArray()));
        return this.getListByResultSet(this.executeQuery(sql, paramList.toArray()));
    }

    @Override
    public int insert(User user) {
        String sql = "INSERT INTO user(username, password, name, gender, birthday, is_admin, mobile, department_id) " +
                "VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
        Object[] params = {user.getUsername(), user.getPassword(), user.getName(), user.getGender(),
                user.getBirthday(), user.getIsAdmin(), user.getMobile(), user.getDepartmentId()};
        return this.executeInsert(sql, params);
    }

    @Override
    public void insert(List<User> users) {
        String sql = "INSERT INTO user(username, password, name, gender, birthday, is_admin, mobile, department_id) " +
                "VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
        List<Object[]> paramsList = new ArrayList<>();
        for (User user : users) {
            Object[] params = {user.getUsername(), user.getPassword(), user.getName(), user.getGender(),
                    user.getBirthday(), user.getIsAdmin(), user.getMobile(), user.getDepartmentId()};
            paramsList.add(params);
        }
        this.executeBatch(sql, paramsList);
    }

    @Override
    public int update(User user) {
        String sql = "UPDATE user SET username=?, password=?, name=?, gender=?, birthday=?, is_admin=?, mobile=?, " +
                "department_id=? WHERE id=?";
        Object[] params = {user.getUsername(), user.getPassword(), user.getName(), user.getGender(),
                user.getBirthday(), user.getIsAdmin(), user.getMobile(), user.getDepartmentId(), user.getId()};
        return this.executeUpdate(sql, params);
    }

    @Override
    public int deleteById(Integer id) {
        String sql = "DELETE FROM user WHERE id=?";
        return this.executeUpdate(sql, id);
    }

    @Override
    public int batchDeleteByIds(String ids) {
        String sql = "DELETE FROM user WHERE id IN(" + ids + ")";
        return this.executeUpdate(sql);
    }

    @Override
    public int getAllCount(UserExample userExample) {
        List<Object> paramList = new ArrayList<>();
        return (int) (long) this.uniqueResult(this.exampleWhereClause(userExample, paramList, false, true),
                paramList.toArray());
    }

    /**
     * 根据UserExample组装where条件以及参数集合
     *
     * @param user      user示例条件
     * @param paramList 参数列表集合
     * @param limit     是否有分页数据
     * @return where部分的sql语句
     */
    private String exampleWhereClause(UserExample user, List<Object> paramList, boolean limit, boolean count) {
        StringBuffer where = new StringBuffer();
        if (user != null) {
            String sql;
            //是否是统计
            if (count) {
                sql = "SELECT COUNT(u.id) FROM user u";
            } else {
                sql = selectSql;
            }
            // 组装关联表
            if (user.isJoinDepartment()) {
                where.append(sql.substring(0, sql.indexOf("FROM user u"))).append(",d.name FROM user u LEFT JOIN " +
                        "department d ON u.department_id=d.id ");
            } else {
                where.append(sql);
            }
            // 组装查询条件
            where.append(" WHERE 1=1 ");
            if (user.getId() != null) {
                where.append(" AND u.id=? ");
                paramList.add(user.getId());
            } else if (user.getIds() != null && !"".equals(user.getIds())) {
                where.append(" AND u.id IN(").append(user.getIds()).append(")");
            }
            if (user.getUsername() != null) {
                where.append(" AND u.username LIKE ? ");
                paramList.add("%" + user.getUsername() + "%");
            }
            if (user.getName() != null && !"".equals(user.getName())) {
                where.append(" AND u.user LIKE ? ");
                paramList.add("%" + user.getName() + "%");
            }
            if (user.getGender() != null) {
                where.append(" AND u.gender=? ");
                paramList.add(user.getGender());
            }
            if (user.getBirthdayBegin() != null) {
                paramList.add(user.getBirthdayBegin());
                if (user.getBirthdayEnd() != null) {
                    where.append(" AND u.birthday BETWEEN ? AND ? ");
                    paramList.add(user.getBirthdayEnd());
                } else {
                    where.append(" AND u.birthday>= ?");
                }
            } else if (user.getBirthdayEnd() != null) {
                where.append(" AND u.birthday <= ? ");
                paramList.add(user.getBirthdayEnd());
            }
            if (user.getIsAdmin() != null) {
                where.append(" AND u.is_admin=? ");
                paramList.add(user.getIsAdmin());
            }
            if (user.getMobile() != null && !"".equals(user.getMobile())) {
                where.append(" AND u.mobile LIKE ? ");
                paramList.add("%" + user.getMobile() + "%");
            }
            if (user.getDepartmentId() != null) {
                where.append(" AND u.department_id=? ");
                paramList.add(user.getDepartmentId());
            } else if (user.getDepartmentIds() != null && !"".equals(user.getDepartmentIds())) {
                where.append(" AND u.department_id IN (").append(user.getDepartmentIds()).append(")");
            }
            if (limit) {
                // 组装分页条件
                if (user.getLimit() != null) {
                    where.append(" LIMIT ?");
                    if (user.getOffset() != null) {
                        where.append(" ,? ");
                        paramList.add(user.getOffset());
                    }
                    paramList.add(user.getLimit());
                }
                // 组装排序条件
                if (user.getOrderByClause() != null && !"".equals(user.getOrderByClause())) {
                    where.append(" ORDER BY ").append(user.getOrderByClause());
                }
            }
        }
        return where.toString();
    }

    private List<User> getListByResultSet(ResultSet rs) {
        List<User> users = null;
        if (rs != null) {
            users = new ArrayList<>();
            try {
                while (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("u.id"));
                    user.setUsername(rs.getString("u.username"));
                    user.setPassword(rs.getString("u.password"));
                    user.setName(rs.getString("u.name"));
                    user.setGender(rs.getByte("u.gender"));
                    // java.sql.Date转换成java.time.LocalDate
                    user.setBirthday(rs.getDate("u.birthday").toLocalDate());
                    user.setIsAdmin(rs.getByte("u.is_admin"));
                    user.setMobile(rs.getString("u.mobile"));
                    user.setDepartmentId(rs.getInt("u.department_id"));
                    // 如果查询了关联部门对象
                    if (isExistColumn(rs, "d.name")) {
                        user.setDepartment(new Department(user.getDepartmentId(), rs.getString("d.name")));
                    }
                    users.add(user);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                closeAll();
            }
        }
        return users;
    }

}

